#!/usr/bin/env python
# -*- coding: utf-8 -*-
import mysql.connector
import uuid
from mysql.connector import pooling


def sync_data():
    # '''源数据库配置 (sysmandb)'''
    src_db = {
        # 'host': '10.1.213.159',
        # 'user': 'GTXS_user',
        # 'password': 'GTXS_user_JG123',
        'host': '10.99.81.155',
        'port': 8306,
        'user': 'jg_usercenter',
        'password': 'Jg_usercenter_20250312',
        'database': 'jg_usercenter'
    }

    # '''目标数据库配置 (jg_usercenter)'''
    dest_db = {
        'host': '10.99.81.155',
        'port': 8306,
        'user': 'jg_usercenter',
        'password': 'Jg_usercenter_20250312',
        'database': 'jg_usercenter'
    }

    try:
        # '''创建数据库连接'''
        # src_conn = mysql.connector.connect(**src_db)
        src_connection_pool = pooling.MySQLConnectionPool(
            pool_name="srcpool",
            pool_size=30,
            pool_reset_session=True,
            **src_db
        )
        src_conn = src_connection_pool.get_connection()

        # dest_conn = mysql.connector.connect(**dest_db)
        dest_connection_pool = pooling.MySQLConnectionPool(
            pool_name="destpool",
            pool_size=5,
            pool_reset_session=True,
            **dest_db
        )
        dest_conn = dest_connection_pool.get_connection()

        src_cursor = src_conn.cursor(dictionary=True)
        dest_cursor = dest_conn.cursor()

        # '''1. 查询源数据,注意数据范围'''
        src_cursor.execute("""
            select id, corp_code, corp_name, user_code, enterprise_type from sys_corp 
                where enterprise_type in ('Carrier', 'InSapEnterprise') 
                and corp_code not in ('2000','C1670341916') 
                order by id
        """)

        # '''2. 准备插入语句（根据实际表结构调整字段）'''

        # '''3. 执行同步'''
        count = 0

        roles = query_default_role(dest_conn)
        for corp in src_cursor:
            processed_user = dict(corp)  # '''创建原始数据的副本'''
            # '''使用Python函数处理特定字段'''
            # processed_user['user_name'] = 'jg' + corp['corp_code']
            # processed_user['user_code'] = 'UC' + corp['corp_code']
            processed_user['enterprise_id'] = corp['corp_code']

            # '''执行插入'''
            # sys_corp
            # insert_corp(processed_user, dest_cursor)
            # sys_corp_log
            # insert_corp_log(processed_user, dest_cursor)
            # sys_depart
            # dept_id = insert_dept(processed_user['enterprise_name'], processed_user['enterprise_id'], dest_cursor)
            # processed_user['depart_ids'] = dept_id
            # sys_user
            # user_id = insert_user(processed_user, dest_cursor)
            user = query_user(processed_user['user_code'], dest_conn)
            processed_user['user_id'] = user['id']
            # corp_app_role
            insert_corp_app_role(processed_user, roles, dest_cursor, dest_conn)

            count += 1
            print(count)

        dest_conn.commit()  # 提交剩余记录
        print("同步完成！总计: %d 条记录" % count)

    except mysql.connector.Error as err:
        print("数据库错误: {}".format(err))
        dest_conn.rollback()
    finally:
        # '''关闭连接'''
        if src_cursor: src_cursor.close()
        if dest_cursor: dest_cursor.close()
        if src_conn: src_conn.close()
        if dest_conn: dest_conn.close()


def query_default_role(dest_conn):
    try:
        cursor = dest_conn.cursor(dictionary=True)

        sql = "select * from sys_role where default_flag = %s"
        cursor.execute(sql, ('1',))
        row_dict = cursor.fetchall()
        return row_dict
    finally:
        if cursor: cursor.close()


def query_user(user_code, dest_conn):
    try:
        cursor = dest_conn.cursor(dictionary=True)

        sql = "select * from sys_user where user_code = %s"
        cursor.execute(sql, (user_code,))
        row_dict = cursor.fetchone()
        return row_dict
    finally:
        if cursor: cursor.close()


def query_user_app(user_code, app_code, dest_conn):
    try:
        cursor = dest_conn.cursor(dictionary=True)

        sql = "select * from sys_user_app where user_code = %s and app_code = %s"
        cursor.execute(sql, (user_code, app_code,))
        row_dict = cursor.fetchone()
        return row_dict
    finally:
        if cursor: cursor.close()


def query_user_role(user_code, role_id, dest_conn):
    try:
        cursor = dest_conn.cursor(dictionary=True)

        sql = "select * from sys_user_role where user_code = %s and role_id = %s"
        cursor.execute(sql, (user_code, role_id,))
        row_dict = cursor.fetchone()
        return row_dict
    finally:
        if cursor: cursor.close()


def insert_corp_app_role(processed_user, roles, dest_cursor, dest_conn):
    # print('insert_corp_app_role')
    corp_code = processed_user['enterprise_id']
    dicts = []
    # d = {'app_code': 'uc', 'corp_type': 'uc_user', 'corp_code': processed_user['enterprise_id']}
    # dicts.append(d)
    if processed_user['enterprise_type'] == 'Consumer':
        wl = {'app_code': 'wl', 'corp_type': 'wl_seller', 'corp_code': corp_code}
        dicts.append(wl)
        ck = {'app_code': 'ck', 'corp_type': 'ck_receiver', 'corp_code': corp_code}
        dicts.append(ck)

    if processed_user['enterprise_type'] == 'Carrier':
        # wl = {'app_code': 'wl', 'corp_type': 'wl_carrier', 'corp_code': corp_code}
        # dicts.append(wl)
        # main = {'app_code': 'main', 'corp_type': 'main_carrier', 'corp_code': corp_code}
        # dicts.append(main)
        ymt = {'app_code': 'ymt', 'corp_type': 'ymt_carrier', 'corp_code': corp_code}
        dicts.append(ymt)

    if processed_user['enterprise_type'] == 'InSapEnterprise':
        # wl = {'app_code': 'wl', 'corp_type': 'wl_shipper', 'corp_code': corp_code}
        # dicts.append(wl)
        # ck = {'app_code': 'ck', 'corp_type': 'ck_shipper', 'corp_code': corp_code}
        # dicts.append(ck)
        # main = {'app_code': 'main', 'corp_type': 'main_user', 'corp_code': corp_code}
        # dicts.append(main)
        ymt = {'app_code': 'ymt', 'corp_type': 'ymt_user', 'corp_code': corp_code}
        dicts.append(ymt)

    if processed_user['enterprise_type'] == 'Supplier':
        wl = {'app_code': 'wl', 'corp_type': 'wl_shipper', 'corp_code': corp_code}
        dicts.append(wl)

    insert_sql = """
        INSERT INTO sys_corp_app_role (app_code, corp_type, corp_code) VALUES (%(app_code)s, %(corp_type)s, %(corp_code)s)
    """

    user_code = processed_user['user_code']
    # user = query_user(dest_conn, processed_user['user_code'])
    for c in dicts:
        # c = {'app_code': 'wl', 'corp_type': 'wl_shipper', 'corp_code': corp_code}
        # sys_corp_app_role
        dest_cursor.execute(insert_sql, c)
        # sys_user_app
        user_app = query_user_app(user_code, c['app_code'], dest_conn)
        if user_app is None:
            insert_user_app(c['app_code'], user_code, dest_cursor)
        # sys_user_role
        insert_default_role(c, user_code, processed_user['user_id'], roles, dest_cursor, dest_conn)

    # user_role user['id']
    # insert_user_role(112, processed_user['user_code'], processed_user['user_id'], dest_cursor)
    # insert_reg_user_role(processed_user, dest_cursor, dest_conn)


def insert_user_app(app_code, user_code, dest_cursor):
    # print('insert_user_app')
    insert_sql = """
        INSERT INTO sys_user_app(user_code, app_code) VALUES (%s, %s)
    """

    dest_cursor.execute(insert_sql, (user_code, app_code))


def insert_default_role(c, user_code, user_id, roles, dest_cursor, dest_conn):
    # print('insert_default_role')

    # roles = query_default_role(dest_conn)
    for role in roles:
        if role['app_code'] == c['app_code'] and role['role_group'] == c['corp_type']:
            user_role = query_user_role(user_code, role['id'], dest_conn)
            if user_role is None:
                insert_user_role(role['id'], user_code, user_id, dest_cursor)


def insert_user_role(role_id, user_code, user_id, dest_cursor):
    insert_sql = """
            INSERT INTO sys_user_role(id, role_id, user_code, user_id) VALUES (%s, %s, %s, %s)
        """
    dest_cursor.execute(insert_sql, (str(uuid.uuid4()), role_id, user_code, user_id))


if __name__ == "__main__":
    sync_data()
